﻿Option Explicit On
Imports Microsoft.VisualBasic
Imports System
Imports System.Xml
Imports System.Data
Imports System.IO
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class ClassDatabase
#Region "Set Parameter"
    Private comm As SqlCommand
#End Region

#Region "Function"

    '*************************************************************************
    '* Open Database Connection 
    '*************************************************************************
    Public Function ConnectionOpen(Optional ByVal connStr As String = "") As SqlConnection
        Dim conn As New SqlConnection
        Try
            If (connStr = "") Then
                connStr = connString
            End If

            conn = New SqlConnection(connStr)
            conn.Open()

        Catch ex As Exception
            ex.Message.ToString()
            conn.Dispose()
            conn = Nothing
        Finally

        End Try
        Return conn
    End Function

    '******************************************************************************
    '* Close Database Connection
    '******************************************************************************
    Public Sub ConnectionClose(ByRef conn As SqlConnection)
        conn.Close()
        conn.Dispose()
    End Sub

    '******************************************************************************
    '* Close Database Connection
    '******************************************************************************
    Public Function GeneratorID(ByRef conn As SqlConnection, ByVal tableName As String) As String
        Dim ret As String = "00000"
        Dim sqlGenID As String = ""
        Dim ds As New DataSet
        If tableName <> "" Then
            Select Case tableName

                Case "Customer"
                    sqlGenID = "Customer_no"
                Case "Customer_Billto"
                    sqlGenID = "Bill_to"
                Case "RelationMeterial"
                    sqlGenID = "Mat_Code"
                Case "Menu"
                    sqlGenID = "Menu_no"
                Case "Booking"
                    sqlGenID = "Booking_no"
                Case "Product"
                    sqlGenID = "Product_no"
                Case "Product_Group"
                    sqlGenID = "Group_no"
                Case "Buyer_Seller"
                    sqlGenID = "BS_no"
                Case "Facility"
                    sqlGenID = "Facility_Code"
                Case "Shore"
                    sqlGenID = "Shore_Code"
                Case "Line"
                    sqlGenID = "Line_Code"
                Case "Loading_Unloading"
                    sqlGenID = "Load_Code"
                Case "Bank"
                    sqlGenID = "Bank_Code"
                Case "Tug"
                    sqlGenID = "Tug_Code"
                Case "Bas_Master"
                    sqlGenID = "Bas_no"
                Case "Booking_Bas_Vessel"
                    sqlGenID = "BV_no"
                Case "Payment_Bank"
                    sqlGenID = "Tracking"
                Case "Maintenance_Wharf"
                    sqlGenID = "Maintenance_id"
                Case "Cause_Delay"
                    sqlGenID = "Cause_Code"
                Case "Action_Operation"
                    sqlGenID = "A_Code"
                Case "Pre_Booking"
                    sqlGenID = "Booking_no"
                Case "OtherService_Head"
                    sqlGenID = "Ots_ID"
                Case "Color"
                    sqlGenID = "Color_Code"
                Case "Stock"
                    sqlGenID = "Stock_Code"
                Case "Slop_Master"
                    sqlGenID = "Slop_Code"
                Case "Slop_Transection"
                    sqlGenID = "Slop_Tran"
            End Select
            ds = BindData(conn, sqlStatement:="Select Top 1 " & sqlGenID & " as ID from " & tableName & " order by convert(int," & sqlGenID & ") desc", tableName:="Gen-ID")
            Try
                If Not ds Is Nothing Then
                    ret = CInt(CInt(ds.Tables("Gen-ID").Rows(0).Item("ID")) + 1).ToString("#####00000")
                    ds.Dispose()
                Else
                    ret = "00001"
                End If
            Catch ex As Exception
                ex.Message.ToString()
            Finally
                ds = Nothing
            End Try
        End If
        Return ret
    End Function

    '*************************************************************************
    '* BindData From Table to Properties
    '*************************************************************************
    Public Function BindData(ByRef conn As SqlConnection, Optional ByVal sqlStatement As String = "", Optional ByVal tableName As String = "") As DataSet
        Dim da As SqlDataAdapter = Nothing
        Dim ds As New DataSet
        Dim s As New SqlCommand


        If (tableName = "") Then
            tableName = "tbMaster"
        End If

        Try
            da = New SqlDataAdapter(sqlStatement, conn)

            da.SelectCommand = New SqlCommand
            da.SelectCommand.CommandType = CommandType.Text
            da.SelectCommand.Connection = conn
            da.SelectCommand.CommandText = sqlStatement
            da.SelectCommand.CommandTimeout = 60000

            da.Fill(ds, tableName)

            If (ds.Tables(tableName).Rows.Count = 0) Then
                ds = Nothing
            End If

            'Dim DA1 As New OleDb.OleDbDataAdapter(SqlStr, conn)
            'Try
            '    CurrentSQLStatement = SqlStr
            '    '==============================
            '    's = New OleDb.OleDbCommand(SqlStr, Conn)
            '    DA1.SelectCommand = New OleDb.OleDbCommand
            '    DA1.SelectCommand.CommandType = CommandType.Text
            '    DA1.SelectCommand.Connection = conn
            '    DA1.SelectCommand.CommandText = SqlStr
            '    DA1.SelectCommand.CommandTimeout = 1000
            '    'DA1.SelectCommand = s
            '    DA1.Fill(DS1, tableName)


        Catch ex As Exception
            ds = Nothing
            ex.Message.ToString()
        Finally
            da.Dispose()
            da = Nothing
        End Try
        Return ds
    End Function

    '*************************************************************************
    '* BindData From Table to Properties
    '*************************************************************************
    Public Function FillDataset(ByRef conn As SqlConnection, ByVal ds As DataSet, Optional ByVal sqlStatement As String = "", Optional ByVal TableName As String = "") As Boolean
        Dim da As SqlDataAdapter
        Dim ret As Boolean

        If (TableName = "") Then
            TableName = "tbMaster"
        End If

        Try

            da = New SqlDataAdapter(sqlStatement, conn)
            da.Fill(ds, TableName)

        Catch ex As Exception
            ex.Message.ToString()
            ret = False
        Finally
            da = Nothing
        End Try
        ret = True
    End Function

    '*************************************************************************
    '* Update Data From Table 
    '*************************************************************************
    Public Function UpdateSql(ByRef conn As SqlConnection, ByVal sqlStatement As String, Optional ByRef tran As SqlTransaction = Nothing) As Boolean
        Dim ret As Boolean = False
        Try
            comm = New SqlCommand(sqlStatement, conn)
            If Not (tran Is Nothing) Then
                comm.Connection = conn
                comm.Transaction = tran
            End If

            If comm.ExecuteNonQuery() > 0 Then
                ret = True
            End If

        Catch ex As Exception
            ex.Message.ToString()
            ret = False
        Finally
            comm.Dispose()
            comm = Nothing
        End Try
        Return ret
    End Function

    '*************************************************************************
    '* Delete Data From Table 
    '*************************************************************************
    Public Function DeleteSql(ByRef conn As SqlConnection, ByVal sqlStatement As String, Optional ByRef tran As SqlTransaction = Nothing) As Boolean
        Dim ret As Boolean = False
        Try
            comm = New SqlCommand(sqlStatement, conn)
            If Not (tran Is Nothing) Then
                comm.Connection = conn
                comm.Transaction = tran
            End If

            If comm.ExecuteNonQuery() > 0 Then
                ret = True
            End If

        Catch ex As Exception
            ex.Message.ToString()
            ret = False
        Finally
            comm.Dispose()
            comm = Nothing
        End Try
        Return ret
    End Function

    '******************************************************************************
    '* Execute sql statement 
    '******************************************************************************
    Public Function ExecuteSql(ByRef conn As SqlConnection, ByVal sqlStatement As String, Optional ByRef tran As SqlTransaction = Nothing, Optional ByRef MessageError As String = "") As Boolean
        Dim ret As Boolean = False
        Try
            comm = New SqlCommand(sqlStatement, conn)
            If Not (tran Is Nothing) Then
                comm.Connection = conn
                comm.Transaction = tran
            End If
            If comm.ExecuteNonQuery() >= 0 Then
                ret = True
            End If

        Catch ex As Exception
            MessageError = ex.Message.ToString()
            ret = False
        Finally
            comm.Dispose()
            comm = Nothing
        End Try
        Return ret
    End Function

    '******************************************************************************
    '* Begin Database Transaction and Return transaction that was created
    '******************************************************************************
    Public Function BeginTran(ByRef conn As SqlConnection) As SqlTransaction
        Dim tran As SqlTransaction
        tran = conn.BeginTransaction
        BeginTran = tran
    End Function

    '**********************************************************************************
    '* End Transaction if Success ret true and false if it not success
    '**********************************************************************************
    Public Function CommitTran(ByRef tran As SqlTransaction) As Boolean
        Dim ret As Boolean = False
        Try
            tran.Commit()
            ret = True
        Catch ex As Exception
            ex.Message.ToString()
            tran.Rollback()
        Finally

        End Try

        Return ret
    End Function

    '********************************************************************************
    '* Rollback transaction 
    '********************************************************************************
    Public Function RollBackTran(ByRef tran As SqlTransaction) As Boolean
        Dim ret As Boolean = False
        Try
            tran.Rollback()
            ret = True
        Catch ex As Exception
            ex.Message.ToString()
            ret = False
        Finally

        End Try

        Return ret
    End Function
#End Region

    '*************************************************************************
    '* Open Database Connection 
    '*************************************************************************
    Public Function ConnectionOpenOleDb(Optional ByVal ConnStr As String = "") As OleDb.OleDbConnection
        Dim Conn As New OleDb.OleDbConnection
        Try
            If (ConnStr = "") Then
                ConnStr = connString
            End If

            Conn = New OleDb.OleDbConnection(ConnStr)
            Conn.Open()
        Catch ex As Exception
            Conn = Nothing
        End Try
        ConnectionOpenOleDb = Conn
    End Function


    '******************************************************************************
    '* Close Database Connection
    '******************************************************************************
    Public Sub ConnectionCloseOleDb(ByRef Conn As OleDb.OleDbConnection)
        Conn.Close()
        Conn.Dispose()
    End Sub


    '*************************************************************************
    '* BindData From Table to Properties
    '*************************************************************************
    Public Function BindDataOleDb(ByRef Conn As OleDb.OleDbConnection, Optional ByVal OrderByString As String = "", Optional ByVal ConditionString As String = "", Optional ByVal CustomSQLStatement As String = "", Optional ByVal TableName As String = "") As DataSet

        Dim DA1 As OleDb.OleDbDataAdapter
        Dim DS1 As New DataSet
        Dim SqlStr As String = ""

        If (TableName = "") Then
            TableName = "BTABLE1"
        End If

        If (CustomSQLStatement = "") Then
            SqlStr = "SELECT * FROM PROJECT_RESOURCES"
            If (ConditionString <> "") Then
                SqlStr = SqlStr & "  " & ConditionString
            End If
            If (OrderByString <> "") Then
                SqlStr = SqlStr & "  " & OrderByString
            End If
        Else
            SqlStr = CustomSQLStatement
        End If

        ' CurrentSQLStatement = SqlStr
        DA1 = New OleDb.OleDbDataAdapter(SqlStr, Conn)
        DA1.Fill(DS1, TableName)

        If (DS1.Tables(TableName).Rows.Count > 0) Then
            BindDataOleDb = DS1
        Else
            Return Nothing
        End If
        DA1 = Nothing

        DS1 = Nothing
    End Function
End Class

